package com.mftimer.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

import static com.mftimer.Configs.DB_TBL_COUNT;

public class DBHelper extends SQLiteOpenHelper {
	private static final String DB_NAME = "mftimer.db";
	public static final ArrayList<String> TBL_NAME = new ArrayList(DB_TBL_COUNT);
	private SQLiteDatabase db;
	
	public DBHelper(Context c) {
		super(c, DB_NAME, null, 4);
		inittbl();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db)
	{
		this.db = db;
	}

	private void inittbl() {
		if (TBL_NAME.contains("resulttb")) return;
		TBL_NAME.add("resulttb");
		for ( int i = 2; i <= DB_TBL_COUNT; i++ )
		{
			TBL_NAME.add("result" + i);
		}
		if (db == null) db = getWritableDatabase();
		db.execSQL("create table if not exists resulttb (id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
		for(int i=2; i<=DB_TBL_COUNT; i++) db.execSQL("create table if not exists result"+i+" (id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
	}

	public int getTblCount() {
		return TBL_NAME.size();
	}

	public void append_tbl()
	{
		int index = TBL_NAME.size() + 1;
		if (db == null) db = getWritableDatabase();
		if ( TBL_NAME.contains("result" + index) ) return;
		TBL_NAME.add("result" + index);
		db.execSQL("create table if not exists result"+index+" (id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
	}

	public void create_tbl(int index)
	{
		if (1 >= index) return;
		if (db == null) db = getWritableDatabase();
		if ( TBL_NAME.contains("result" + index) ) return;
		TBL_NAME.add("result" + index);
		db.execSQL("create table if not exists result"+index+" (id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
	}

	public void delete_tbl(int idx)
	{
		if ( idx >= TBL_NAME.size() || 0 == idx ) return;
		if (db == null) db = getWritableDatabase();

		//Log.d("MFTimer", "===============drop " + idx);
		db.execSQL("drop table if exists result" + (idx + 1));
		TBL_NAME.remove(idx);

		for ( int i = idx; i < TBL_NAME.size(); i++ )
		{
			//Log.d("MFTimer", "=======" + i + " " + TBL_NAME.size() +"========rename " + TBL_NAME.get(i) + " to result" + (i + 1));
			db.execSQL("alter table " + TBL_NAME.get(i) + " rename to result" + (i + 1));
			TBL_NAME.set(i, "result" + (i + 1));
		}
	}

	public void insert(int i, ContentValues values) {
		if (db == null) db = getWritableDatabase();
		db.insert(TBL_NAME.get(i), null, values);
	}
	
	public Cursor query(int i) {
		if (db == null) db = getWritableDatabase();
		//Log.d("MFTimer", "===============query " + i + " " + TBL_NAME.size());
		Cursor c = db.query(TBL_NAME.get(i), null, null, null, null, null, null);
		return c;
	}
	
	public void del(int i, int id) {
		if (db == null) db = getWritableDatabase();
		db.delete(TBL_NAME.get(i), "id=?", new String[] {String.valueOf(id)});
	}
	
	public void clear(int i) {
		this.getWritableDatabase().delete(TBL_NAME.get(i), null, null);
	}
	
	public void update(int i, int id, int resp, int resd) {
		if (db == null) db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("resp", resp);
		cv.put("resd", resd);
		db.update(TBL_NAME.get(i), cv, "id=?",new String[] {String.valueOf(id)});
	}
	
	public void update(int i, int id, String note) {
		if (db == null) db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("note", note);
		db.update(TBL_NAME.get(i), cv, "id=?",new String[] {String.valueOf(id)});
	}
	
	public void close() {
		if (db != null) {db.close();db = null;}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
		if(oldVer > newVer) return;
		this.db = db;
		db.execSQL("alter table resulttb add time text;");
		db.execSQL("alter table resulttb add note text;");
		for(int i=1; i<7; i++)db.execSQL("alter table resulttb add p"+i+" integer;");
		if(oldVer < 3) {
			for(int i=2; i<=DB_TBL_COUNT; i++) db.execSQL("create table result"+i+"(id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
		}
		else if(oldVer < 4){
			for(int i=2; i<10; i++) {
				db.execSQL("alter table result"+i+" add time text;");
				db.execSQL("alter table result"+i+" add note text;");
				for(int j=1; j<7; j++)db.execSQL("alter table result"+i+" add p"+j+" integer;");
			}
			for(int i=10; i<=DB_TBL_COUNT; i++) db.execSQL("create table result"+i+"(id integer not null,rest integer not null,resp integer not null,resd integer not null,scr text not null,time text,note text,p1 integer,p2 integer,p3 integer,p4 integer,p5 integer,p6 integer);");
		}
		//for(int i=1;i<9;i++)db.execSQL(CREATE_TBL[i]);
	}
}